"""
通用查询API路由
支持基于过滤器的通用数据库查询接口
"""

from typing import List, Optional
from fastapi import APIRouter, Depends, HTTPException, status, Body
from sqlalchemy.orm import Session
from sqlalchemy import select, Table, MetaData

from src.models.generic_filter import GenericFilter, QueryResponse, TableQueryInfo
from src.services.generic_query_service import GenericQueryService
from src.config.database import get_db
from src.utils.logging import get_logger

router = APIRouter(prefix="/query", tags=["query"])
logger = get_logger(__name__)


@router.post("/tables/{table_name}", response_model=QueryResponse)
async def query_table(
    table_name: str,
    filter_obj: GenericFilter = Body(...),
    db: Session = Depends(get_db)
):
    """
    通用表查询接口
    
    支持复杂的条件查询、排序、分页和搜索功能
    
    **示例请求体:**
    ```json
    {
      "conditions": [
        {
          "field": "age",
          "operator": "greater_than",
          "value": 25
        },
        {
          "operator": "and",
          "conditions": [
            {
              "field": "name",
              "operator": "like",
              "value": "John"
            },
            {
              "field": "status",
              "operator": "equals",
              "value": "active"
            }
          ]
        }
      ],
      "search": "keyword",
      "search_fields": ["name", "description"],
      "sort_by": [
        {
          "field": "created_at",
          "order": "desc"
        }
      ],
      "page": 1,
      "page_size": 20,
      "include_total": true
    }
    ```
    """
    try:
        return GenericQueryService.query_table(db, table_name, filter_obj)
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"查询表 {table_name} 失败: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )


@router.post("/tables/{table_name}/string", response_model=QueryResponse)
async def query_table_with_string(
    table_name: str,
    condition_str: str = Body(..., embed=True, description="SQL-like条件字符串，如 'age > 25 AND name LIKE \"%John%\"'"),
    search: Optional[str] = Body(None, description="全文搜索关键词"),
    search_fields: Optional[List[str]] = Body(None, description="搜索字段列表"),
    sort_by: Optional[List[dict]] = Body(None, description="排序字段列表，如 [{'field': 'created_at', 'order': 'desc'}]"),
    page: int = Body(1, ge=1, description="页码"),
    page_size: int = Body(20, ge=1, le=100, description="每页大小"),
    include_total: bool = Body(True, description="是否包含总数统计"),
    db: Session = Depends(get_db)
):
    """
    使用SQL-like条件字符串查询表
    
    支持简单的SQL语法进行条件查询，便于快速构建查询条件
    
    **示例请求体:**
    ```json
    {
      "condition_str": "age > 25 AND (name LIKE '%John%' OR status = 'active')",
      "search": "keyword",
      "search_fields": ["name", "description"],
      "sort_by": [
        {
          "field": "created_at",
          "order": "desc"
        }
      ],
      "page": 1,
      "page_size": 20,
      "include_total": true
    }
    ```
    
    **支持的语法:**
    - 比较操作符: =, !=, >, >=, <, <=
    - 逻辑操作符: AND, OR
    - 模糊匹配: LIKE, ILIKE
    - 空值检查: IS NULL, IS NOT NULL
    - 范围查询: IN, NOT IN, BETWEEN
    - 括号分组: (condition1 AND condition2)
    """
    try:
        # 转换sort_by格式
        sort_fields = None
        if sort_by:
            from src.models.generic_filter import SortField, SortOrder
            sort_fields = []
            for sort_item in sort_by:
                sort_fields.append(SortField(
                    field=sort_item['field'],
                    order=SortOrder(sort_item.get('order', 'asc'))
                ))
        
        return GenericQueryService.query_table_with_string(
            db=db,
            table_name=table_name,
            condition_str=condition_str,
            search=search,
            search_fields=search_fields,
            sort_by=sort_fields,
            page=page,
            page_size=page_size,
            include_total=include_total
        )
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"使用字符串条件查询表 {table_name} 失败: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"查询失败: {str(e)}"
        )


@router.get("/tables/{table_name}/info", response_model=TableQueryInfo)
async def get_table_query_info(
    table_name: str,
    db: Session = Depends(get_db)
):
    """获取表的查询信息（用于构建查询界面）"""
    try:
        columns_info = GenericQueryService.get_table_columns(db, table_name)
        
        # 获取主键信息
        from sqlalchemy import inspect
        inspector = inspect(db.get_bind())
        pk_info = inspector.get_pk_constraint(table_name)
        primary_keys = pk_info.get('constrained_columns', []) or []
        
        columns = []
        for col_info in columns_info:
            columns.append({
                'name': col_info['name'],
                'type': col_info['type'],
                'nullable': col_info['nullable']
            })
        
        return {
            'table_name': table_name,
            'columns': columns,
            'primary_keys': primary_keys
        }
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取表 {table_name} 查询信息失败: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取查询信息失败: {str(e)}"
        )


@router.get("/tables/{table_name}/sample")
async def get_table_sample_data(
    table_name: str,
    limit: int = 10,
    db: Session = Depends(get_db)
):
    """获取表的样本数据"""
    try:
        # 构建简单的查询获取样本数据
        filter_obj = GenericFilter(
            conditions=None,
            search=None,
            search_fields=None,
            sort_by=None,
            page=1,
            page_size=limit,
            include_total=False
        )
        return GenericQueryService.query_table(db, table_name, filter_obj)
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取表 {table_name} 样本数据失败: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取样本数据失败: {str(e)}"
        )


@router.get("/supported-operators")
async def get_supported_operators():
    """获取支持的查询操作符列表"""
    from src.models.generic_filter import Operator, LogicalOperator, SortOrder
    
    return {
        "field_operators": [
            {"value": op.value, "label": op.value.replace("_", " ").title()}
            for op in Operator
        ],
        "logical_operators": [
            {"value": op.value, "label": op.value.upper()}
            for op in LogicalOperator
        ],
        "sort_orders": [
            {"value": op.value, "label": op.value.upper()}
            for op in SortOrder
        ]
    }


@router.get("/tables/{table_name}/distinct-values/{column_name}")
async def get_distinct_values(
    table_name: str,
    column_name: str,
    db: Session = Depends(get_db)
):
    """获取表中某列的唯一值列表（用于下拉选择）"""
    try:
        # 验证表是否存在
        if not GenericQueryService._table_exists(db, table_name):
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"表 '{table_name}' 不存在"
            )
        
        # 验证列是否存在
        columns_info = GenericQueryService.get_table_columns(db, table_name)
        column_exists = any(col['name'] == column_name for col in columns_info)
        if not column_exists:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"列 '{column_name}' 在表 '{table_name}' 中不存在"
            )
        
        # 查询唯一值
        metadata = MetaData()
        table = Table(table_name, metadata, autoload_with=db.get_bind())
        column = table.c.get(column_name)
        
        if column is None:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"列 '{column_name}' 在表 '{table_name}' 中不存在"
            )
        
        query = select(column).distinct().order_by(column)
        result = db.execute(query)
        values = [row[0] for row in result if row[0] is not None]
        
        return {"values": values}
        
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"获取表 {table_name} 列 {column_name} 唯一值失败: {e}")
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取唯一值失败: {str(e)}"
        )
